/**
 * 
 */
package vn.edu.fpt.xml.hrm.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Properties;
import java.util.Set;

import vn.edu.fpt.xml.hrm.common.AppConstant;
import vn.edu.fpt.xml.hrm.pojo.Project;

/**
 * @author Yoshimi
 *
 */
public class ProjectBean {
	private String dbms;
	private String serverName;
	private String port;
	private String dbName;
	private boolean integratedSecurity;
	private String username;
	private String password;

	/**
	 * 
	 */
	public ProjectBean() {
		dbms = AppConstant.DB_DBMS;
		serverName = AppConstant.DB_SERVER_NAME;
		port = AppConstant.DB_PORT;
		dbName = AppConstant.DB_NAME;
		integratedSecurity = AppConstant.DB_INTEGRATED_SECURITY;
		username = AppConstant.DB_USERNAME;
		password = AppConstant.DB_PASSWORD;
	}
	/**
	 * @return the dbms
	 */
	protected String getDbms() {
		return dbms;
	}
	/**
	 * @param dbms the dbms to set
	 */
	protected void setDbms(String dbms) {
		this.dbms = dbms;
	}
	/**
	 * @return the serverName
	 */
	protected String getServerName() {
		return serverName;
	}
	/**
	 * @param serverName the serverName to set
	 */
	protected void setServerName(String serverName) {
		this.serverName = serverName;
	}
	/**
	 * @return the port
	 */
	protected String getPort() {
		return port;
	}
	/**
	 * @param port the port to set
	 */
	protected void setPort(String port) {
		this.port = port;
	}
	/**
	 * @return the dbName
	 */
	protected String getDbName() {
		return dbName;
	}
	/**
	 * @param dbName the dbName to set
	 */
	protected void setDbName(String dbName) {
		this.dbName = dbName;
	}
	/**
	 * @return the integratedSecurity
	 */
	protected boolean isIntegratedSecurity() {
		return integratedSecurity;
	}
	/**
	 * @param integratedSecurity the integratedSecurity to set
	 */
	protected void setIntegratedSecurity(boolean integratedSecurity) {
		this.integratedSecurity = integratedSecurity;
	}
	/**
	 * @return the username
	 */
	protected String getUsername() {
		return username;
	}
	/**
	 * @param username the username to set
	 */
	protected void setUsername(String username) {
		this.username = username;
	}
	/**
	 * @return the password
	 */
	protected String getPassword() {
		return password;
	}
	/**
	 * @param password the password to set
	 */
	protected void setPassword(String password) {
		this.password = password;
	}

	/**
	 * @param fromDate
	 * @param toDate
	 * @param projectId
	 * @param employeeId
	 * @param status
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public Set<Project> getProjectList(int managerId) throws SQLException, ClassNotFoundException {
		Set<Project> result = null;
		Connection connection = null;
		String url = null;
		Properties properties = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		String sql = null;
		Project project = null;
		try {
			// 1. setup connection
			// database url
			url = "jdbc:" + dbms + "://" + serverName + ":" + port;
			// connection arguments
			properties = new Properties();
			properties.put("databaseName", dbName);
			if (integratedSecurity) {
				properties.put("integratedSecurity", true);
			} else {
				properties.put("username", username);
				properties.put("password", password);
			}
			// specify the driver to be used
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			// actualize connection
			connection = DriverManager.getConnection(url, properties);
			
			// 2. create query
			// 2.1. create the query
			sql = "SELECT DISTINCT p.\"ProjectID\" AS \"id\""
								+ ", p.\"ProjectName\" AS \"name\""
								+ ", p.\"StartDate\" AS \"startDate\""
								+ ", p.\"EndDate\" AS \"endDate\""
								+ ", p.\"Status\" AS \"status\""
					+ " FROM \"Project\" AS p"
					+ " LEFT JOIN \"Role\" AS r ON p.\"ProjectID\" = r.\"ProjectID\""
					+ " WHERE r.\"EmployeeID\" = ? AND r.\"RoleTypeID\" = 3";
			// 2.2. turn the query to a preparedStatement
			statement = connection.prepareStatement(sql);
			// 2.3. add parameters to the statement
			statement.setInt(1, managerId);
			
			// 3. execute query
			resultSet = statement.executeQuery();
			
			// 4. read result
			result = new HashSet<Project>();
			while (resultSet.next()) {
				project = new Project();
				project.setId(resultSet.getInt("id"));
				project.setName(resultSet.getString("name"));
				project.setStartDate(resultSet.getDate("startDate"));
				project.setEndDate(resultSet.getDate("endDate"));
				project.setStatus(resultSet.getString("status"));
				result.add(project);
			}
		} finally {
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return result;
	}

}
